package db;

import bean.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.List;

public class UserDao {

    private static final String CREATESL = "insert into user(username,pass,phone) " +
            "values(?,password(?),?)";

    private static final String SELECTALL = "select id,username,pass,phone from user";

    private static final String DELETEQL = "delete from user where id = ?";

    private static final String FINDBYIDQL = "select id,username,pass,phone from user where id = ?";

    private static final String LOGINQL = "select id,username,phone from user where username = ? and pass = password(?);";

    private QueryRunner runner;

    public UserDao(QueryRunner runner) {
        this.runner = runner;
    }

    //增删改查
    public void create(User user) throws SQLException {
        runner.execute(CREATESL, user.getUsername(), user.getPass(), user.getPhone());

    }

    public void update(User user) {

    }

    public void delete(int id) throws SQLException {
        runner.execute(DELETEQL, id);
    }

    public User findById(int id) throws SQLException {
        ResultSetHandler<User> resultSetHandler = new BeanHandler<>(User.class);
        return runner.query(FINDBYIDQL, resultSetHandler, id);
    }

    public List<User> find() throws SQLException {
        ResultSetHandler<List<User>> resultSetHandler = new BeanListHandler<>(User.class);
        return runner.query(SELECTALL, resultSetHandler);
    }

    public User login(String username, String password) throws SQLException {
        ResultSetHandler<User> resultSetHandler = new BeanHandler<>(User.class);
        return runner.query(LOGINQL,resultSetHandler,username,password);
    }
}
